package com.stx.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.stx.jdbc.JdbcUtil;
import com.stx.entity.MedicineForm;
import com.stx.entity.MedicineType;
import com.stx.entity.TMedicine;

public class MedicineDao {
	Connection con = null;
	PreparedStatement pst = null;
	//展示展示所有药品信息
	public List<TMedicine> showAllmedicine() {
		List<TMedicine> medlist=new ArrayList<TMedicine>();
		try {
			con=JdbcUtil.getCon();
			pst=con.prepareStatement("select  mid ,mname,m.mtypeid,mformid,mfactoryid,prescription,expiration, mcount,mfunction from tbl_prescription p,tbl_department d,tbl_medicine m where p.did=d.did and p.mtypeid=m.mtypeid");
			ResultSet rs =pst.executeQuery();
			while(rs.next()){
				TMedicine med=new TMedicine();
				med.setMcount(rs.getString("mcount"));
				med.setMfactoryid(rs.getString("mfactoryid"));
				med.setMformid(rs.getString("mformid"));
				med.setMfunction(rs.getString("mfunction"));
				med.setMid(rs.getString("mid"));
				med.setMname(rs.getString("mname"));
				System.out.println(med.getMname());
				med.setMtypeid(rs.getString("mtypeid"));
				med.setExpiration(rs.getString("expiration"));
				med.setPrescription(rs.getString("prescription"));
				System.out.println(med.getPrescription());
				medlist.add(med);
			}
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return medlist;
	}
	//展示所有药品信息（科室）
	public List<TMedicine> showAllmedicine(String did) {
		List<TMedicine> medlist=new ArrayList<TMedicine>();
		try {
			con=JdbcUtil.getCon();
			pst=con.prepareStatement("select  mid ,mname,m.mtypeid,mformid,mfactoryid,prescription,expiration, mcount,mfunction from tbl_prescription p,tbl_department d,tbl_medicine m where p.did=d.did and p.mtypeid=m.mtypeid and p.did=?");
			pst.setString(1, did);
			ResultSet rs =pst.executeQuery();
			while(rs.next()){
				TMedicine med=new TMedicine();
				med.setMcount(rs.getString("mcount"));
				med.setMfactoryid(rs.getString("mfactoryid"));
				med.setMformid(rs.getString("mformid"));
				med.setMfunction(rs.getString("mfunction"));
				med.setMid(rs.getString("mid"));
				med.setMname(rs.getString("mname"));
				System.out.println(med.getMname());
				med.setMtypeid(rs.getString("mtypeid"));
				med.setExpiration(rs.getString("expiration"));
				med.setPrescription(rs.getString("prescription"));
				System.out.println(med.getPrescription());
				medlist.add(med);
			}
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return medlist;
	}
		
	
	//展示某一药品类型id
	public int findmTypeid(String ftypename){
		try {
			con = JdbcUtil.getCon();
			pst = con.prepareStatement("select mtypeid from tbl_medicineType where mtypename = ?");
			pst.setString(1, ftypename);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				return rs.getInt("mtypeid");
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	//展示某一药品剂型id
	public int findmFormid(String formname){
		try {
			con = JdbcUtil.getCon();
			pst = con.prepareStatement("select mformid from tbl_medicineForm where mformname = ?");
			pst.setString(1, formname);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				return rs.getInt("mformid");
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	//展示药品剂型表
	public List<MedicineForm> showMForm(){
		
		List<MedicineForm> MF = new ArrayList<MedicineForm>();
		try {
			con = JdbcUtil.getCon();
			pst = con.prepareStatement("select * from tbl_medicineForm");
			
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				MedicineForm mf = new MedicineForm();
				mf.setMfid(rs.getString("mformid"));
				mf.setMfname(rs.getString("mformname"));
				MF.add(mf);
			}
			
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		return MF;
	}
	//展示药品类型表
	public List<MedicineType> showMType(){
		List<MedicineType> MT = new ArrayList<MedicineType>();
		try {
			con = JdbcUtil.getCon();
			pst = con.prepareStatement("select * from tbl_medicineType mt ,tbl_prescription p  where mt.mtypeid=p.mtypeid");
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				MedicineType mt = new MedicineType();
				mt.setMtid(rs.getString("mtypeid"));
				mt.setMtname(rs.getString("mtypename"));
				MT.add(mt);
			}
			
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		return MT;
	}
	//展示药品类型表（科室）
	public List<MedicineType> showMType(String did){
		List<MedicineType> MT = new ArrayList<MedicineType>();
		try {
			con = JdbcUtil.getCon();
			pst = con.prepareStatement("select * from tbl_medicineType mt ,tbl_prescription p  where mt.mtypeid=p.mtypeid and p.did = ?");
			pst.setString(1, did);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				MedicineType mt = new MedicineType();
				mt.setMtid(rs.getString("mtypeid"));
				mt.setMtname(rs.getString("mtypename"));
				MT.add(mt);
			}
			
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		return MT;
	}
	//增添药品
	public boolean addmedicine(String mname,String mid,String foid,String fid,String prescription,String expiration,String mcount,String mfunction){
		try {
			con = JdbcUtil.getCon();
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			try {
				Date date = null;
				date = format.parse(expiration);
			} catch (ParseException e1) {
				
				e1.printStackTrace();
			}
			java.sql.Date sqlDate=new java.sql.Date(new java.util.Date().getTime());
			pst = con.prepareStatement("insert into tbl_medicine values(seq_medicine.nextval,?,?,?,?,?,?,?,?)");
			pst.setString(1, mname);
			pst.setString(2, mid);
			pst.setString(3, foid);
			pst.setString(4, fid);
			pst.setString(5,prescription);
			pst.setDate(6, sqlDate);
			pst.setString(7, mcount);
			pst.setString(8, mfunction);
			if(pst.executeUpdate()!=0){
				return true;
			}
			
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		return false;
		
	}
}

